/**
 * 
 */
package com.magus.project.yddemo.service;

import java.sql.Date;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.magus.framework.core.persistence.JpaSearchUtils;
import com.magus.framework.core.persistence.SearchFilter;
import com.magus.framework.core.repository.BaseRepository;
import com.magus.framework.core.service.BaseService;
import com.magus.project.yddemo.entity.IndicatorMapperData;
import com.magus.project.yddemo.entity.IndicatorMapperReport6;
import com.magus.project.yddemo.repository.IndicatorMapperReport6Repository;

/**
 * @author wenyu.han
 * @date 2018-03-30 09:13:39
 */
@Service
@Transactional(readOnly = true)
public class IndicatorMapperReport6Service extends BaseService<IndicatorMapperReport6, String> {

	@Autowired
	private IndicatorMapperReport6Repository reportRepository;

	@Autowired
	private IndicatorMapperDataService mapperDataService;

	@Override
	protected BaseRepository<IndicatorMapperReport6, String> getBaseRepository() {
		return reportRepository;
	}

	// 数据起始行号
	static final Integer ROW_INDEX = 12;

	/**
	 * 每行的固定列用##符号分开
	 */
	static final String[] ALL_ROW_NAME = new String[] { "合计##00##null", "电厂明细##01##null",
			"茂名臻能热点有限公司##01001##76656459-8" };

	/**
	 * 供热设备台数指标
	 */
	static final String[] GRSB_ALL = new String[] { "MMP_GRSBTS_M_M_M_SUM_M_MANL" };

	/**
	 * 期末供热设备容量指标
	 */
	static final String[] QMGR_ALL = new String[] { "MMP_MPRL_M_M_M_SUM_M_CTST" };

	/**
	 * 供热量指标
	 */
	static final String[] GRL_ALL = new String[] { "MMP_GRL_M_M_M_SUM_M_CSST" };

	/**
	 * 原煤指标
	 */
	static final String[] YM_ALL = new String[] { "MMP_GRHYM_M_M_M_SUM_M_CSST" };

	/**
	 * 原油指标
	 */
	static final String[] YY_ALL = new String[] { "MMP_GRHYY_M_M_M_SUM_M_CSST" };

	/**
	 * 天然气指标
	 */
	static final String[] TRQ_ALL = new String[] { "MMP_GRHRQ_M_M_M_SUM_M_CSST" };

	/**
	 * 厂用电量指标
	 */
	static final String[] YDL_ALL = new String[] { "MMP_GRCYDL_M_M_M_SUM_M_CSST" };

	/**
	 * 煤折指标
	 */
	static final String[] MZ_ALL = new String[] { "MMP_GRCYDL_M_M_M_SUM_M_CSST" };

	/**
	 * 油折指标
	 */
	static final String[] YZ_ALL = new String[] { "MMP_GRYYZB_M_M_M_SUM_M_CSST" };

	/**
	 * 天然气折指标
	 */
	static final String[] TRQZ_ALL = new String[] { "MMP_GRRQZB_M_M_M_SUM_M_CSST" };

	/**
	 * 报表左上角信息设置
	 */
	static final String[] REPORT_DESC = new String[] { "CEC111-6000千瓦及以上电厂供热生产情况（年报）", "424997" };

	/**
	 * 更新IndicatorMapperReport6数据
	 */
	@Transactional
	public Boolean updateDatas(Long time) {
		Date date = new Date(time);
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		// 设置用户选择的月份的当月为结束时间
		long endTime = time;
		// 查询IndicatorMapperReport6表中当前时间戳是否存在数据
		List<IndicatorMapperReport6> historyReports = reportRepository.findByTimeOrderByRowIndexAsc(endTime);
		List<IndicatorMapperReport6> reportData = null;
		// 如果存在数据,则需要保留之前页面手动录入的数据。
		if (historyReports.size() != 0) {
			reportData = setUpdateReportData(historyReports, endTime);
		} else {// 如果不存在数据，则直接更新数据
			reportData = new ArrayList<>();
			for (int i = 0; i < ALL_ROW_NAME.length; i++) {
				IndicatorMapperReport6 report = new IndicatorMapperReport6();
				// 设置时间
				report.setTime(endTime);
				// 设置行号
				report.setRowIndex(i + 1);
				String[] rowName = ALL_ROW_NAME[i].split("##");
				// 设置电压等级
				report.setZbmc(rowName[0]);
				// 设置指标代码
				report.setZbdm(rowName[1]);
				// 设置组织机构代码
				if (rowName[2].equals("null")) {
					report.setZzjgdm("");
				} else {
					report.setZzjgdm(rowName[2]);
				}
				report.setGrsbts(0);
				report.setQmgrsbrl(0.0);
				report.setGrl(0.0);
				report.setGrrlxh_ym(0.0);
				report.setGrrlxh_ry(0.0);
				report.setGrrlxh_trq(0.0);
				report.setGrcyd_cydl(0.0);
				report.setGrcyd_cydlv(0.0);
				report.setGrbzml_hj(0.0);
				report.setGrbzml_mz(0.0);
				report.setGrbzml_yz(0.0);
				report.setGrbzml_trqz(0.0);
				report.setGrbzmh(0.0);
				reportData.add(report);
			}
			reportData = setUpdateReportData(reportData, endTime);
		}
		List<IndicatorMapperReport6> save = reportRepository.save(reportData);
		return save.size() > 0 ? true : false;
	}

	/**
	 * 从MapperData更新的数据，保存之前手动录入的数据。
	 * 
	 * @param historyReports
	 * @param beginTime
	 * @param endTime
	 * @param oldTime
	 * @return
	 */
	private List<IndicatorMapperReport6> setUpdateReportData(List<IndicatorMapperReport6> historyReports,
			Long endTime) {
		List<String> indicatorCodes = null;
		for (int i = 0; i < 3; i++) {
			if (i == 1) {
				continue;
			}
			// 设置第1和第3行数据
			indicatorCodes = Arrays.asList(GRSB_ALL);
			List<IndicatorMapperData> findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			// 1. 计算供热设备台数
			int grsbSum = (int) mapperDataListSum(findAll);
			historyReports.get(i).setGrsbts(grsbSum);

			// 2. 计算期末供热设备容量
			indicatorCodes = Arrays.asList(QMGR_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double qmgrSum = mapperDataListSum(findAll);
			historyReports.get(i).setQmgrsbrl(qmgrSum);

			// 3. 计算供热量
			indicatorCodes = Arrays.asList(GRL_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double grlSum = mapperDataListSum(findAll);
			historyReports.get(i).setGrl(grlSum);

			// 4. 计算原煤
			indicatorCodes = Arrays.asList(YM_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double ymSum = mapperDataListSum(findAll);
			historyReports.get(i).setGrrlxh_ym(ymSum);

			// 5. 计算原油
			indicatorCodes = Arrays.asList(YM_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double yySum = mapperDataListSum(findAll);
			historyReports.get(i).setGrrlxh_ry(yySum);

			// 6. 计算天然气
			indicatorCodes = Arrays.asList(TRQ_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double trqSum = mapperDataListSum(findAll);
			historyReports.get(i).setGrrlxh_trq(trqSum);

			// 7.厂用电量
			indicatorCodes = Arrays.asList(YDL_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double ydlSum = mapperDataListSum(findAll);
			historyReports.get(i).setGrcyd_cydl(ydlSum);

			// 8.厂用电率
			double cydlSum = 0.0;
			if(grlSum!=0.0) {
				cydlSum = ydlSum * 10000 / grlSum;
			}
			historyReports.get(i).setGrcyd_cydlv(cydlSum);

			// 9.煤折
			indicatorCodes = Arrays.asList(MZ_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double mzSum = mapperDataListSum(findAll);
			historyReports.get(i).setGrbzml_mz(mzSum);

			// 10.油折
			indicatorCodes = Arrays.asList(YZ_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double yzSum = mapperDataListSum(findAll);
			historyReports.get(i).setGrbzml_yz(yzSum);

			// 11.天然气折
			indicatorCodes = Arrays.asList(TRQZ_ALL);
			findAll = findMapperDatas(indicatorCodes, endTime, endTime);
			double trqzSum = mapperDataListSum(findAll);
			historyReports.get(i).setGrbzml_trqz(trqzSum);

			// 12.合计
			double hjSum = mzSum + yzSum + trqzSum;
			historyReports.get(i).setGrbzml_hj(hjSum);

			// 13.供热标准煤耗
			double grbzmhSum = 0.0;
			if(grlSum!=0.0) {
				grbzmhSum = hjSum * 1000 / grlSum;
			}
			historyReports.get(i).setGrbzmh(grbzmhSum);
		}

		return historyReports;
	}

	/**
	 * 导出报表
	 */
	public HSSFWorkbook exportReport(List<IndicatorMapperReport6> reportDataList) {
		// 创建工作簿
		HSSFWorkbook workBook = new HSSFWorkbook();
		// 创建工作表 工作表的名字叫helloWorld
		HSSFSheet sheet = workBook.createSheet("工作表1");
		// 创建左上角固定报表描述表格
		sheet = creatRepotDesc(workBook, sheet, reportDataList.get(0).getTime());

		// 创建下方主报表
		sheet = creatMainRepot(workBook, sheet, reportDataList);
		return workBook;
	}

	/**
	 * 创建左上角报表
	 * 
	 * @param sheet
	 * @param time
	 * @return
	 */
	public HSSFSheet creatRepotDesc(HSSFWorkbook workBook, HSSFSheet sheet, Long time) {
		// 设置字体样式
		HSSFFont font = workBook.createFont();
		font.setColor(HSSFFont.COLOR_RED);

		// 创建第1行
		HSSFRow row_1 = sheet.createRow(0);
		HSSFCell row_1_cell_1 = row_1.createCell(0);
		row_1_cell_1.setCellValue("*");
		HSSFCell row_1_cell_2 = row_1.createCell(1);
		row_1_cell_2.setCellValue("");
		HSSFCell row_1_cell_3 = row_1.createCell(2);
		row_1_cell_3.setCellValue("1.本表头供系统识别使用；\r\n" + "2.各单位根据具体情况填写单位代码、单位名称、填报人；\r\n" + "3.本表头报告期必须与此报表报告期一致；\r\n"
				+ "4.指标代码必须按照规律编；\r\n" + "5.不可改动报表格式。");
		// 创建第2行
		HSSFRow row_2 = sheet.createRow(1);
		HSSFCell row_2_cell_1 = row_2.createCell(0);
		HSSFRichTextString richString = new HSSFRichTextString("广东粤电集团有限公司");
		richString.applyFont(font);
		row_2_cell_1.setCellValue(richString);
		HSSFCell row_2_cell_2 = row_2.createCell(1);
		row_2_cell_2.setCellValue("（单位名称）");

		// 创建第3行
		HSSFRow row_3 = sheet.createRow(2);
		HSSFCell row_3_cell_1 = row_3.createCell(0);
		richString = new HSSFRichTextString("9808000001");
		richString.applyFont(font);
		row_3_cell_1.setCellValue(richString);
		HSSFCell row_3_cell_2 = row_3.createCell(1);
		row_3_cell_2.setCellValue("（单位代码）");

		// 创建第4行
		HSSFRow row_4 = sheet.createRow(3);
		HSSFCell row_4_cell_1 = row_4.createCell(0);
		richString = new HSSFRichTextString("统计专责");
		richString.applyFont(font);
		row_4_cell_1.setCellValue(richString);
		HSSFCell row_4_cell_2 = row_4.createCell(1);
		row_4_cell_2.setCellValue("（填报人）");

		// 创建第5行
		HSSFRow row_5 = sheet.createRow(4);
		HSSFCell row_5_cell_1 = row_5.createCell(0);
		row_5_cell_1.setCellValue(REPORT_DESC[0]);
		HSSFCell row_5_cell_2 = row_5.createCell(1);
		row_5_cell_2.setCellValue("（表名）");

		// 创建第6行
		HSSFRow row_6 = sheet.createRow(5);
		HSSFCell row_6_cell_1 = row_6.createCell(0);
		row_6_cell_1.setCellValue(REPORT_DESC[1]);
		HSSFCell row_6_cell_2 = row_6.createCell(1);
		row_6_cell_2.setCellValue("（表号）");

		// 创建第7行
		HSSFRow row_7 = sheet.createRow(6);
		HSSFCell row_7_cell_1 = row_7.createCell(0);
		row_7_cell_1.setCellValue("1");
		HSSFCell row_7_cell_2 = row_7.createCell(1);
		row_7_cell_2.setCellValue("（期别）");

		// 创建第8行
		Calendar cal = Calendar.getInstance();
		Date date = new Date(time);
		cal.setTime(date);
		HSSFRow row_8 = sheet.createRow(7);
		HSSFCell row_8_cell_1 = row_8.createCell(0);
		row_8_cell_1.setCellValue(new HSSFRichTextString(String.valueOf(cal.get(Calendar.YEAR))));
		HSSFCell row_8_cell_2 = row_8.createCell(1);
		row_8_cell_2.setCellValue("年");
		HSSFCell row_8_cell_3 = row_8.createCell(2);
		row_8_cell_3.setCellValue(new HSSFRichTextString(String.valueOf(cal.get(Calendar.MONTH) + 1)));
		HSSFCell row_8_cell_4 = row_8.createCell(3);
		row_8_cell_4.setCellValue("月");
		HSSFCell row_8_cell_5 = row_8.createCell(4);
		row_8_cell_5.setCellValue("");

		sheet.addMergedRegion(new CellRangeAddress(0, 6, 2, 2));

		return sheet;
	}

	/**
	 * 创建下方主要表格
	 * 
	 * @param sheet
	 * @param reportDataList
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public HSSFSheet creatMainRepot(HSSFWorkbook workBook, HSSFSheet sheet,
			List<IndicatorMapperReport6> reportDataList) {
		// 创建红色居中样式
		HSSFCellStyle cellStyle = workBook.createCellStyle();
		// 设置居中样式
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 设置字体样式
		HSSFFont font = workBook.createFont();
		font.setColor(HSSFFont.COLOR_RED);
		cellStyle.setFont(font);

		// 创建第9行
		HSSFRow row_9 = sheet.createRow(8);
		HSSFCell row_9_cell_1 = row_9.createCell(0);
		row_9_cell_1.setCellValue("6000千瓦及以上电厂发电生产情况");

		// 创建第10行
		HSSFRow row_10 = sheet.createRow(9);
		HSSFCell row_10_cell_1 = row_10.createCell(0);
		row_10_cell_1.setCellValue("综合填报单位：  （章）");
		HSSFCell row_10_cell_2 = row_10.createCell(1);
		row_10_cell_2.setCellValue("");
		HSSFCell row_10_cell_3 = row_10.createCell(2);
		row_10_cell_3.setCellValue("");
		HSSFCell row_10_cell_4 = row_10.createCell(3);
		row_10_cell_4.setCellValue("");
		HSSFCell row_10_cell_5 = row_10.createCell(4);
		row_10_cell_5.setCellValue("");
		HSSFCell row_10_cell_6 = row_10.createCell(5);
		row_10_cell_6.setCellValue("");
		HSSFCell row_10_cell_7 = row_10.createCell(6);
		row_10_cell_7.setCellValue("YYYY");
		HSSFCell row_10_cell_8 = row_10.createCell(10);
		row_10_cell_8.setCellValue("");
		HSSFCell row_10_cell_9 = row_10.createCell(11);
		row_10_cell_9.setCellValue("");
		HSSFCell row_10_cell_10 = row_10.createCell(12);
		row_10_cell_10.setCellValue("");
		HSSFCell row_10_cell_11 = row_10.createCell(13);
		row_10_cell_11.setCellValue("");
		HSSFCell row_10_cell_12 = row_10.createCell(14);
		row_10_cell_12.setCellValue("");
		HSSFCell row_10_cell_13 = row_10.createCell(15);
		row_10_cell_13.setCellValue("");

		// 创建第11行
		HSSFRow row_11 = sheet.createRow(10);
		HSSFCell row_11_cell_1 = row_11.createCell(0);
		row_11_cell_1.setCellValue("电厂名称");
		HSSFCell row_11_cell_2 = row_11.createCell(1);
		row_11_cell_2.setCellValue("指标代码");
		HSSFCell row_11_cell_3 = row_11.createCell(2);
		row_11_cell_3.setCellValue("组织机构代码");
		HSSFCell row_11_cell_4 = row_11.createCell(3);
		row_11_cell_4.setCellValue("供热设备台数");
		HSSFCell row_11_cell_5 = row_11.createCell(4);
		row_11_cell_5.setCellValue("期末发电设备容量");
		HSSFCell row_11_cell_6 = row_11.createCell(5);
		row_11_cell_6.setCellValue("供热量");
		HSSFCell row_11_cell_7 = row_11.createCell(6);
		row_11_cell_7.setCellValue("供热燃料消耗");
		HSSFCell row_11_cell_8 = row_11.createCell(9);
		row_11_cell_8.setCellValue("供热厂用电");
		HSSFCell row_11_cell_9 = row_11.createCell(11);
		row_11_cell_9.setCellValue("供热标准煤量（吨）");
		HSSFCell row_11_cell_10 = row_11.createCell(15);
		row_11_cell_10.setCellValue("供热标准煤耗");

		// 创建第12行
		HSSFRow row_12 = sheet.createRow(11);
		HSSFCell row_12_cell_1 = row_12.createCell(6);
		row_12_cell_1.setCellValue("原煤");
		HSSFCell row_12_cell_2 = row_12.createCell(7);
		row_12_cell_2.setCellValue("原油");
		HSSFCell row_12_cell_3 = row_12.createCell(8);
		row_12_cell_3.setCellValue("天然气");
		HSSFCell row_12_cell_4 = row_12.createCell(9);
		row_12_cell_4.setCellValue("厂用电量");
		HSSFCell row_12_cell_5 = row_12.createCell(10);
		row_12_cell_5.setCellValue("厂用电率");
		HSSFCell row_12_cell_6 = row_12.createCell(11);
		row_12_cell_6.setCellValue("合计");
		HSSFCell row_12_cell_7 = row_12.createCell(12);
		row_12_cell_7.setCellValue("其    中");

		// 创建第13行
		HSSFRow row_13 = sheet.createRow(12);
		HSSFCell row_13_cell_1 = row_13.createCell(3);
		row_13_cell_1.setCellValue("（台）");
		HSSFCell row_13_cell_2 = row_13.createCell(4);
		row_13_cell_2.setCellValue("（千瓦）");
		HSSFCell row_13_cell_3 = row_13.createCell(5);
		row_13_cell_3.setCellValue("（吉焦）");
		HSSFCell row_13_cell_4 = row_13.createCell(6);
		row_13_cell_4.setCellValue("（吨）");
		HSSFCell row_13_cell_5 = row_13.createCell(7);
		row_13_cell_5.setCellValue("（吨）");
		HSSFCell row_13_cell_6 = row_13.createCell(8);
		row_13_cell_6.setCellValue("(万立方米）");
		HSSFCell row_13_cell_7 = row_13.createCell(9);
		row_13_cell_7.setCellValue("（万千瓦时）");
		HSSFCell row_13_cell_8 = row_13.createCell(10);
		row_13_cell_8.setCellValue("（千瓦时/吉焦）");
		HSSFCell row_13_cell_9 = row_13.createCell(12);
		row_13_cell_9.setCellValue("煤折");
		HSSFCell row_13_cell_10 = row_13.createCell(13);
		row_13_cell_10.setCellValue("油折");
		HSSFCell row_13_cell_11 = row_13.createCell(14);
		row_13_cell_11.setCellValue("天然气折");
		HSSFCell row_13_cell_12 = row_13.createCell(15);
		row_13_cell_12.setCellValue("（千克/吉焦）");

		// 创建第14行
		HSSFRow row_14 = sheet.createRow(12);
		HSSFCell row_14_cell_1 = row_14.createCell(0);
		row_14_cell_1.setCellValue("甲");
		HSSFCell row_14_cell_2 = row_14.createCell(1);
		row_14_cell_2.setCellValue("1");
		HSSFCell row_14_cell_3 = row_14.createCell(2);
		row_14_cell_3.setCellValue("2");
		HSSFCell row_14_cell_4 = row_14.createCell(3);
		row_14_cell_4.setCellValue("3");
		HSSFCell row_14_cell_5 = row_14.createCell(4);
		row_14_cell_5.setCellValue("4");
		HSSFCell row_14_cell_6 = row_14.createCell(5);
		row_14_cell_6.setCellValue("5");
		HSSFCell row_14_cell_7 = row_14.createCell(6);
		row_14_cell_7.setCellValue("6");
		HSSFCell row_14_cell_8 = row_14.createCell(7);
		row_14_cell_8.setCellValue("7");
		HSSFCell row_14_cell_9 = row_14.createCell(8);
		row_14_cell_9.setCellValue("8");
		HSSFCell row_14_cell_10 = row_14.createCell(9);
		row_14_cell_10.setCellValue("9");
		HSSFCell row_14_cell_11 = row_14.createCell(10);
		row_14_cell_11.setCellValue("10");
		HSSFCell row_14_cell_12 = row_14.createCell(11);
		row_14_cell_12.setCellValue("11");
		HSSFCell row_14_cell_13 = row_14.createCell(12);
		row_14_cell_13.setCellValue("12");
		HSSFCell row_14_cell_14 = row_14.createCell(13);
		row_14_cell_14.setCellValue("13");
		HSSFCell row_14_cell_15 = row_14.createCell(14);
		row_14_cell_15.setCellValue("14");
		HSSFCell row_14_cell_16 = row_14.createCell(15);
		row_14_cell_16.setCellValue("15");

		for (IndicatorMapperReport6 report : reportDataList) {
			HSSFRow row = sheet.createRow(report.getRowIndex() + ROW_INDEX);
			// 设置第1列
			HSSFCell cell_1 = row.createCell(0);
			cell_1.setCellValue(report.getZbmc());
			// 设置第2列
			HSSFCell cell_2 = row.createCell(1);
			cell_2.setCellValue(report.getZbdm());
			// 设置第3列
			HSSFCell cell_3 = row.createCell(2);
			cell_3.setCellValue(report.getZzjgdm());
			// 设置第4列
			HSSFCell cell_4 = row.createCell(3);
			cell_4.setCellValue(report.getGrsbts());
			// 设置第5列
			HSSFCell cell_5 = row.createCell(4);
			cell_5.setCellValue(report.getQmgrsbrl());
			// 设置第6列
			HSSFCell cell_6 = row.createCell(5);
			cell_6.setCellValue(report.getGrl());
			// 设置第7列
			HSSFCell cell_7 = row.createCell(6);
			cell_7.setCellValue(report.getGrrlxh_ym());
			// 设置第8列
			HSSFCell cell_8 = row.createCell(7);
			cell_8.setCellValue(report.getGrrlxh_ry());
			// 设置第9列
			HSSFCell cell_9 = row.createCell(8);
			cell_9.setCellValue(report.getGrrlxh_trq());
			// 设置第10列
			HSSFCell cell_10 = row.createCell(9);
			cell_10.setCellValue(report.getGrcyd_cydl());
			// 设置第11列
			HSSFCell cell_11 = row.createCell(10);
			cell_11.setCellValue(report.getGrcyd_cydlv());
			// 设置第12列
			HSSFCell cell_12 = row.createCell(11);
			cell_12.setCellValue(report.getGrbzml_hj());
			// 设置第13列
			HSSFCell cell_13 = row.createCell(12);
			cell_13.setCellValue(report.getGrbzml_mz());
			// 设置第14列
			HSSFCell cell_14 = row.createCell(13);
			cell_14.setCellValue(report.getGrbzml_yz());
			// 设置第15列
			HSSFCell cell_15 = row.createCell(14);
			cell_15.setCellValue(report.getGrbzml_trqz());
			// 设置第16列
			HSSFCell cell_16 = row.createCell(15);
			cell_16.setCellValue(report.getGrbzmh());
		}

		// 合并6000千瓦及以上电厂供热生产情况单元格
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 15));
		// 合并YYYY
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 6, 10));
		// 合并电厂名称单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 12, 0, 0));
		// 合并指标代码单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 12, 1, 1));
		// 合并组织机构代码单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 12, 2, 2));
		// 合并供热设备台数单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 3, 3));

		// 合并期末发电设备容量单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 4, 4));

		// 合并供热量单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 5, 5));

		// 合并供热燃料消耗单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 6, 8));

		// 合并供热厂用电单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 9, 10));

		// 合并供热标准煤量（吨）单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 11, 14));
		// 合并合计单元格
		sheet.addMergedRegion(new CellRangeAddress(11, 12, 11, 11));
		// 合并其 中单元格
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 12, 14));

		// 合并供热标准煤耗单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 15, 15));
		return sheet;
	}

	/**
	 * 设置MapperDataList查询方法
	 */
	private List<IndicatorMapperData> findMapperDatas(List<String> indicatorCodes, Long beginTime, Long endTime) {
		List<SearchFilter> searchFilters = new ArrayList<>();
		searchFilters.add(SearchFilter.in("indicatorCode", indicatorCodes));
		searchFilters.add(SearchFilter.ge("time", beginTime));
		searchFilters.add(SearchFilter.le("time", endTime));
		Specification<IndicatorMapperData> buildAndSpec = JpaSearchUtils.buildAndSpec(searchFilters);
		List<IndicatorMapperData> findAll = mapperDataService.findAll(buildAndSpec);
		return findAll;
	}

	/**
	 * 设置MapperDataList求和方法
	 */
	private double mapperDataListSum(List<IndicatorMapperData> mapperDataList) {
		return mapperDataList.stream().mapToDouble(x -> x.getValue()).summaryStatistics().getSum();
	}

	/**
	 * 查询报表数据
	 * 
	 * @param time
	 * @return
	 */
	public List<IndicatorMapperReport6> findByTime(Long time) {
		return reportRepository.findByTimeOrderByRowIndexAsc(time);
	}
}
